package org.ccu.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.ccu.common.ConnectionManager;
import org.ccu.vo.Building;
import org.ccu.vo.Comment;

public class CommentDAO {
	private static Connection conn = ConnectionManager.getConnection();
	private static Statement st = null;
	private static ResultSet rs = null;
	
	
	public static boolean saveComment(int buildingid, String content,
			String username) {

		try {
			st = conn.createStatement();
			String sql = "insert into myc(building_id,comment_id,comment_content,user_id) values ("+buildingid+",(select max(comment_id)+1 from myc),'"+content+"',(select user_id from myu where user_name='"+username+"'))";
			System.out.println(sql);
			st.execute(sql);
			
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return true;

	}

	public static List<Comment> getComment(String buildingid) {
		List<Comment> bs = new ArrayList<Comment>();
		try {
			
			st = conn.createStatement();
			String sql = "select * from(select b.building_id,b.title,k.user_name,k.comment_content,k.comment_id from building b,(select u.user_name,c.building_id,c.comment_content,c.comment_id from myc c,myu u where c.user_id=u.user_id order by c.comment_id) k where k.building_id=b.building_id)where building_id="+buildingid;	
			System.out.println(sql);
			rs = st.executeQuery(sql);

			while (rs.next()) {
				Comment b=new Comment(rs.getString("USER_NAME"),rs.getString("TITLE"),rs.getString("COMMENT_CONTENT"),rs.getInt("comment_id"));
				bs.add(b);
			}
			return bs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			Boolean b = ConnectionManager.close(st, rs);
			return bs;
		}//buildingid tiaozhuang

	}

}
